package com.saunter.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ImportExcelUtils {
    public static ExcelData loadExcelFile(InputStream stream, String sheet_name) throws IOException {
        return loadExcelFile(stream, sheet_name, true, true);
    }

    /**
     *
     * @param stream
     * @param sheet_name
     * @param has_title excel是否含有标题行
     * @param format_date 是否对excel中出现的Date对象格式化
     * @return
     * @throws IOException
     */
    public static ExcelData loadExcelFile(InputStream stream, String sheet_name, boolean has_title, boolean format_date) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(stream);
        XSSFSheet sheet = workbook.getSheet(sheet_name);
        if(null == sheet) {
            return null;
        }
        ExcelData excelData = new ExcelData();

        List<List<Object>> excel_body = new ArrayList<List<Object>>();
        List<Object> row_data;

        short title_cell_count = 0;
        for(Row row : sheet) {
            if(row.getRowNum() == 0 && has_title) {
                List<String>row_title = new ArrayList<String>();
                for(Cell cell : row) {
                    try {
                        row_title.add(getCellValueByCell(cell, format_date).toString());
                    }catch (NullPointerException e) {
                        row_title.add("");
                    }
                }
                //getLastCellNum只能获取到非空列的最大列数值
                title_cell_count = row.getLastCellNum();
                excelData.setTitles(row_title);
            }else {
                row_data = new ArrayList<Object>();
                if(0 == title_cell_count) {
                    title_cell_count = row.getLastCellNum();
                }
                //为了兼顾空的cell，必须使用这种循环；for(Cell cell : row)会自动过滤null的cell
                for(int cell_index=0; cell_index < title_cell_count; cell_index++ ) {
                    row_data.add(getCellValueByCell(row.getCell(cell_index), format_date));
                }
                excel_body.add(row_data);
            }
        }
        if(0 < excel_body.size()) {
            excelData.setRows(excel_body);
        }
        return excelData;
    }

    /**
     * 根据cell的类型来获取cell的值
     * @param cell
     * @param format_date 是否对Date对象格式化
     * @return
     */
    public static Object getCellValueByCell(Cell cell, boolean format_date) {
        if (cell==null || cell.toString().trim().equals("")) {
            return "";
        }
        Object cellValue;
        switch (cell.getCellType()) {
            case STRING: //字符串类型
                cellValue= cell.getStringCellValue().trim();
                break;
            case BOOLEAN:  //布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC: //数值类型
                //日期类型
                if(HSSFDateUtil.isCellDateFormatted(cell)) {
                    Short date_format = cell.getCellStyle().getDataFormat();
                    cellValue = cell.getDateCellValue();
                    if(format_date) {
                        if(14 <= date_format && 17 >= date_format){
                            cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cellValue);
                        }else if(18 <= date_format && 22 >=date_format || 45 == date_format || 46 == date_format || 47 == date_format) {
                            cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cellValue);
                        }
                    }
                }else {
                    cellValue = new DecimalFormat("###################.###########").format(cell.getNumericCellValue());
                }

                break;
            default: //其它类型，取空串吧
                cellValue = "";
                break;
        }
        return cellValue;
    }
}
